{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Using Null\n",
    "\n",
    "- teacher\n",
    "\n",
    "id\t| dept\t| name\t| phone\t| mobile\n",
    "----|-------|-------|-------|-----\n",
    "101\t| 1 | Shrivell\t| 2753 | 07986 555 1234\n",
    "102\t| 1\t| Throd\t    | 2754 | 07122 555 1920\n",
    "103\t| 1\t| Splint\t| 2293\t|\n",
    "104 |\t| Spiregrain | 3287\t|\n",
    "105 | 2\t| Cutflower\t | 3212 | 07996 555 6574\n",
    "106 |\t| Deadyawn | 3345 |\t\n",
    "... |      |        |        |\n",
    "\n",
    "- dept\n",
    "\n",
    "id\t| name\n",
    "----|----\n",
    "1\t| Computing\n",
    "2\t| Design\n",
    "3\t| Engineering\n",
    "... |\n",
    "\n",
    "### Teachers and Departments\n",
    "The school includes many departments. Most teachers work exclusively for a single department. Some teachers have no department.\n",
    "\n",
    "[Selecting NULL values](https://sqlzoo.net/wiki/Selecting_NULL_values)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "─ \u001b[1mAttaching packages\u001b[22m ──────────────────── tidyverse 1.3.0 ─\n",
      "\n",
      "\u001b[32m✔\u001b[39m \u001b[34mggplot2\u001b[39m 3.3.0     \u001b[32m✔\u001b[39m \u001b[34mpurrr  \u001b[39m 0.3.4\n",
      "\u001b[32m✔\u001b[39m \u001b[34mtibble \u001b[39m 3.0.1     \u001b[32m✔\u001b[39m \u001b[34mdplyr  \u001b[39m 0.8.5\n",
      "\u001b[32m✔\u001b[39m \u001b[34mtidyr  \u001b[39m 1.0.2     \u001b[32m✔\u001b[39m \u001b[34mstringr\u001b[39m 1.4.0\n",
      "\u001b[32m✔\u001b[39m \u001b[34mreadr  \u001b[39m 1.3.1     \u001b[32m✔\u001b[39m \u001b[34mforcats\u001b[39m 0.5.0\n",
      "\n",
      "─ \u001b[1mConflicts\u001b[22m ───────────────────── tidyverse_conflicts() ─\n",
      "\u001b[31m✖\u001b[39m \u001b[34mdplyr\u001b[39m::\u001b[32mfilter()\u001b[39m masks \u001b[34mstats\u001b[39m::filter()\n",
      "\u001b[31m✖\u001b[39m \u001b[34mdplyr\u001b[39m::\u001b[32mlag()\u001b[39m    masks \u001b[34mstats\u001b[39m::lag()\n",
      "\n"
     ]
    },
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      "Password? ····\n"
     ]
    }
   ],
   "source": [
    "library(tidyverse)\n",
    "library(DBI)\n",
    "library(getPass)\n",
    "drv <- switch(Sys.info()['sysname'],\n",
    "             Windows=\"PostgreSQL Unicode(x64)\",\n",
    "             Darwin=\"/usr/local/lib/psqlodbcw.so\",\n",
    "             Linux=\"PostgreSQL\")\n",
    "con <- dbConnect(\n",
    "  odbc::odbc(),\n",
    "  driver = drv,\n",
    "  Server = \"localhost\",\n",
    "  Database = \"sqlzoo\",\n",
    "  UID = \"postgres\",\n",
    "  PWD = getPass(\"Password?\"),\n",
    "  Port = 5432\n",
    ")\n",
    "options(repr.matrix.max.rows=20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. NULL, INNER JOIN, LEFT JOIN, RIGHT JOIN\n",
    "\n",
    "List the teachers who have NULL for their department.\n",
    "\n",
    "> _Why we cannot use =_   \n",
    "> You might think that the phrase dept=NULL would work here but it doesn't - you can use the phrase dept IS NULL\n",
    "> \n",
    "> _That's not a proper explanation._  \n",
    "> No it's not, but you can read a better explanation at Wikipedia:NULL."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "teacher <- dbReadTable(con, 'teacher')\n",
    "dept <- dbReadTable(con, 'dept')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 2 × 1</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Spiregrain</td></tr>\n",
       "\t<tr><td>Deadyawn  </td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 2 × 1\n",
       "\\begin{tabular}{l}\n",
       " name\\\\\n",
       " <chr>\\\\\n",
       "\\hline\n",
       "\t Spiregrain\\\\\n",
       "\t Deadyawn  \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 2 × 1\n",
       "\n",
       "| name &lt;chr&gt; |\n",
       "|---|\n",
       "| Spiregrain |\n",
       "| Deadyawn   |\n",
       "\n"
      ],
      "text/plain": [
       "  name      \n",
       "1 Spiregrain\n",
       "2 Deadyawn  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "teacher %>% \n",
    "    filter(is.na(dept)) %>%\n",
    "    select(name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.\n",
    "Note the INNER JOIN misses the teachers with no department and the departments with no teacher."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 4 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>teacher</th><th scope=col>dept</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Shrivell </td><td>Computing</td></tr>\n",
       "\t<tr><td>Throd    </td><td>Computing</td></tr>\n",
       "\t<tr><td>Splint   </td><td>Computing</td></tr>\n",
       "\t<tr><td>Cutflower</td><td>Design   </td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 4 × 2\n",
       "\\begin{tabular}{ll}\n",
       " teacher & dept\\\\\n",
       " <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\t Shrivell  & Computing\\\\\n",
       "\t Throd     & Computing\\\\\n",
       "\t Splint    & Computing\\\\\n",
       "\t Cutflower & Design   \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 4 × 2\n",
       "\n",
       "| teacher &lt;chr&gt; | dept &lt;chr&gt; |\n",
       "|---|---|\n",
       "| Shrivell  | Computing |\n",
       "| Throd     | Computing |\n",
       "| Splint    | Computing |\n",
       "| Cutflower | Design    |\n",
       "\n"
      ],
      "text/plain": [
       "  teacher   dept     \n",
       "1 Shrivell  Computing\n",
       "2 Throd     Computing\n",
       "3 Splint    Computing\n",
       "4 Cutflower Design   "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "teacher %>% \n",
    "    inner_join(dept, by=c(dept=\"id\")) %>%\n",
    "    select(name.x, name.y) %>%\n",
    "    rename(teacher=name.x, dept=name.y)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.\n",
    "Use a different JOIN so that all teachers are listed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 6 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>teacher</th><th scope=col>dept</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Shrivell  </td><td>Computing</td></tr>\n",
       "\t<tr><td>Throd     </td><td>Computing</td></tr>\n",
       "\t<tr><td>Splint    </td><td>Computing</td></tr>\n",
       "\t<tr><td>Spiregrain</td><td>NA       </td></tr>\n",
       "\t<tr><td>Cutflower </td><td>Design   </td></tr>\n",
       "\t<tr><td>Deadyawn  </td><td>NA       </td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 6 × 2\n",
       "\\begin{tabular}{ll}\n",
       " teacher & dept\\\\\n",
       " <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\t Shrivell   & Computing\\\\\n",
       "\t Throd      & Computing\\\\\n",
       "\t Splint     & Computing\\\\\n",
       "\t Spiregrain & NA       \\\\\n",
       "\t Cutflower  & Design   \\\\\n",
       "\t Deadyawn   & NA       \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 6 × 2\n",
       "\n",
       "| teacher &lt;chr&gt; | dept &lt;chr&gt; |\n",
       "|---|---|\n",
       "| Shrivell   | Computing |\n",
       "| Throd      | Computing |\n",
       "| Splint     | Computing |\n",
       "| Spiregrain | NA        |\n",
       "| Cutflower  | Design    |\n",
       "| Deadyawn   | NA        |\n",
       "\n"
      ],
      "text/plain": [
       "  teacher    dept     \n",
       "1 Shrivell   Computing\n",
       "2 Throd      Computing\n",
       "3 Splint     Computing\n",
       "4 Spiregrain NA       \n",
       "5 Cutflower  Design   \n",
       "6 Deadyawn   NA       "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "teacher %>%\n",
    "    left_join(dept, by=c(dept=\"id\")) %>%\n",
    "    select(name.x, name.y) %>% \n",
    "    rename(teacher=name.x, dept=name.y)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4.\n",
    "Use a different JOIN so that all departments are listed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 5 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>teacher</th><th scope=col>dept</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Shrivell </td><td>Computing  </td></tr>\n",
       "\t<tr><td>Throd    </td><td>Computing  </td></tr>\n",
       "\t<tr><td>Splint   </td><td>Computing  </td></tr>\n",
       "\t<tr><td>Cutflower</td><td>Design     </td></tr>\n",
       "\t<tr><td>NA       </td><td>Engineering</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 5 × 2\n",
       "\\begin{tabular}{ll}\n",
       " teacher & dept\\\\\n",
       " <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\t Shrivell  & Computing  \\\\\n",
       "\t Throd     & Computing  \\\\\n",
       "\t Splint    & Computing  \\\\\n",
       "\t Cutflower & Design     \\\\\n",
       "\t NA        & Engineering\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 5 × 2\n",
       "\n",
       "| teacher &lt;chr&gt; | dept &lt;chr&gt; |\n",
       "|---|---|\n",
       "| Shrivell  | Computing   |\n",
       "| Throd     | Computing   |\n",
       "| Splint    | Computing   |\n",
       "| Cutflower | Design      |\n",
       "| NA        | Engineering |\n",
       "\n"
      ],
      "text/plain": [
       "  teacher   dept       \n",
       "1 Shrivell  Computing  \n",
       "2 Throd     Computing  \n",
       "3 Splint    Computing  \n",
       "4 Cutflower Design     \n",
       "5 NA        Engineering"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "teacher %>% \n",
    "    right_join(dept, by=c(dept=\"id\")) %>%\n",
    "    select(name.x, name.y) %>%\n",
    "    rename(teacher=name.x, dept=name.y)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Using the [COALESCE](https://sqlzoo.net/wiki/COALESCE) function\n",
    "\n",
    "\n",
    "Use COALESCE to print the mobile number. Use the number '07986 444 2266' if there is no number given. **Show teacher name and mobile number or '07986 444 2266'**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 6 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th><th scope=col>mobile</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Shrivell  </td><td>07986 555 1234</td></tr>\n",
       "\t<tr><td>Throd     </td><td>07122 555 1920</td></tr>\n",
       "\t<tr><td>Splint    </td><td>07986 444 2266</td></tr>\n",
       "\t<tr><td>Spiregrain</td><td>07986 444 2266</td></tr>\n",
       "\t<tr><td>Cutflower </td><td>07996 555 6574</td></tr>\n",
       "\t<tr><td>Deadyawn  </td><td>07986 444 2266</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 6 × 2\n",
       "\\begin{tabular}{ll}\n",
       " name & mobile\\\\\n",
       " <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\t Shrivell   & 07986 555 1234\\\\\n",
       "\t Throd      & 07122 555 1920\\\\\n",
       "\t Splint     & 07986 444 2266\\\\\n",
       "\t Spiregrain & 07986 444 2266\\\\\n",
       "\t Cutflower  & 07996 555 6574\\\\\n",
       "\t Deadyawn   & 07986 444 2266\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 6 × 2\n",
       "\n",
       "| name &lt;chr&gt; | mobile &lt;chr&gt; |\n",
       "|---|---|\n",
       "| Shrivell   | 07986 555 1234 |\n",
       "| Throd      | 07122 555 1920 |\n",
       "| Splint     | 07986 444 2266 |\n",
       "| Spiregrain | 07986 444 2266 |\n",
       "| Cutflower  | 07996 555 6574 |\n",
       "| Deadyawn   | 07986 444 2266 |\n",
       "\n"
      ],
      "text/plain": [
       "  name       mobile        \n",
       "1 Shrivell   07986 555 1234\n",
       "2 Throd      07122 555 1920\n",
       "3 Splint     07986 444 2266\n",
       "4 Spiregrain 07986 444 2266\n",
       "5 Cutflower  07996 555 6574\n",
       "6 Deadyawn   07986 444 2266"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "teacher %>% mutate(\n",
    "    mobile=replace_na(mobile, '07986 444 2266')) %>%\n",
    "    select(name, mobile)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6.\n",
    "Use the COALESCE function and a LEFT JOIN to print the teacher name and department name. Use the string 'None' where there is no department."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 6 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>teacher</th><th scope=col>dept</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Shrivell  </td><td>Computing</td></tr>\n",
       "\t<tr><td>Throd     </td><td>Computing</td></tr>\n",
       "\t<tr><td>Splint    </td><td>Computing</td></tr>\n",
       "\t<tr><td>Spiregrain</td><td>None     </td></tr>\n",
       "\t<tr><td>Cutflower </td><td>Design   </td></tr>\n",
       "\t<tr><td>Deadyawn  </td><td>None     </td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 6 × 2\n",
       "\\begin{tabular}{ll}\n",
       " teacher & dept\\\\\n",
       " <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\t Shrivell   & Computing\\\\\n",
       "\t Throd      & Computing\\\\\n",
       "\t Splint     & Computing\\\\\n",
       "\t Spiregrain & None     \\\\\n",
       "\t Cutflower  & Design   \\\\\n",
       "\t Deadyawn   & None     \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 6 × 2\n",
       "\n",
       "| teacher &lt;chr&gt; | dept &lt;chr&gt; |\n",
       "|---|---|\n",
       "| Shrivell   | Computing |\n",
       "| Throd      | Computing |\n",
       "| Splint     | Computing |\n",
       "| Spiregrain | None      |\n",
       "| Cutflower  | Design    |\n",
       "| Deadyawn   | None      |\n",
       "\n"
      ],
      "text/plain": [
       "  teacher    dept     \n",
       "1 Shrivell   Computing\n",
       "2 Throd      Computing\n",
       "3 Splint     Computing\n",
       "4 Spiregrain None     \n",
       "5 Cutflower  Design   \n",
       "6 Deadyawn   None     "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "teacher %>%\n",
    "    left_join(dept, by=c(dept=\"id\")) %>%\n",
    "    select(name.x, name.y) %>%\n",
    "    rename(teacher=name.x, dept=name.y) %>%\n",
    "    mutate(dept=replace_na(dept, 'None'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7.\n",
    "Use COUNT to show the number of teachers and the number of mobile phones."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A tibble: 1 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>n_name</th><th scope=col>n_mobile</th></tr>\n",
       "\t<tr><th scope=col>&lt;int&gt;</th><th scope=col>&lt;int&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>6</td><td>3</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 1 × 2\n",
       "\\begin{tabular}{ll}\n",
       " n\\_name & n\\_mobile\\\\\n",
       " <int> & <int>\\\\\n",
       "\\hline\n",
       "\t 6 & 3\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 1 × 2\n",
       "\n",
       "| n_name &lt;int&gt; | n_mobile &lt;int&gt; |\n",
       "|---|---|\n",
       "| 6 | 3 |\n",
       "\n"
      ],
      "text/plain": [
       "  n_name n_mobile\n",
       "1 6      3       "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "as_tibble(c(\n",
    "    teacher %>% \n",
    "    tally(!is.na(.$name), name='n_name'), \n",
    "    teacher %>% \n",
    "    tally(!is.na(.$mobile), name='n_mobile'))\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8.\n",
    "Use COUNT and GROUP BY **dept.name** to show each department and the number of staff. Use a RIGHT JOIN to ensure that the Engineering department is listed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A tibble: 3 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name.y</th><th scope=col>n</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;int&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Computing  </td><td>3</td></tr>\n",
       "\t<tr><td>Design     </td><td>1</td></tr>\n",
       "\t<tr><td>Engineering</td><td>0</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 3 × 2\n",
       "\\begin{tabular}{ll}\n",
       " name.y & n\\\\\n",
       " <chr> & <int>\\\\\n",
       "\\hline\n",
       "\t Computing   & 3\\\\\n",
       "\t Design      & 1\\\\\n",
       "\t Engineering & 0\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 3 × 2\n",
       "\n",
       "| name.y &lt;chr&gt; | n &lt;int&gt; |\n",
       "|---|---|\n",
       "| Computing   | 3 |\n",
       "| Design      | 1 |\n",
       "| Engineering | 0 |\n",
       "\n"
      ],
      "text/plain": [
       "  name.y      n\n",
       "1 Computing   3\n",
       "2 Design      1\n",
       "3 Engineering 0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "teacher %>% \n",
    "    right_join(dept, by=c(dept=\"id\")) %>%\n",
    "     count(name.y, wt=!is.na(name.x))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9. Using [CASE](https://sqlzoo.net/wiki/CASE)\n",
    "\n",
    "\n",
    "Use CASE to show the **name** of each teacher followed by 'Sci' if the teacher is in **dept** 1 or 2 and 'Art' otherwise."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 6 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name.x</th><th scope=col>flag</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Shrivell  </td><td>Sci</td></tr>\n",
       "\t<tr><td>Throd     </td><td>Sci</td></tr>\n",
       "\t<tr><td>Splint    </td><td>Sci</td></tr>\n",
       "\t<tr><td>Spiregrain</td><td>Art</td></tr>\n",
       "\t<tr><td>Cutflower </td><td>Sci</td></tr>\n",
       "\t<tr><td>Deadyawn  </td><td>Art</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 6 × 2\n",
       "\\begin{tabular}{ll}\n",
       " name.x & flag\\\\\n",
       " <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\t Shrivell   & Sci\\\\\n",
       "\t Throd      & Sci\\\\\n",
       "\t Splint     & Sci\\\\\n",
       "\t Spiregrain & Art\\\\\n",
       "\t Cutflower  & Sci\\\\\n",
       "\t Deadyawn   & Art\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 6 × 2\n",
       "\n",
       "| name.x &lt;chr&gt; | flag &lt;chr&gt; |\n",
       "|---|---|\n",
       "| Shrivell   | Sci |\n",
       "| Throd      | Sci |\n",
       "| Splint     | Sci |\n",
       "| Spiregrain | Art |\n",
       "| Cutflower  | Sci |\n",
       "| Deadyawn   | Art |\n",
       "\n"
      ],
      "text/plain": [
       "  name.x     flag\n",
       "1 Shrivell   Sci \n",
       "2 Throd      Sci \n",
       "3 Splint     Sci \n",
       "4 Spiregrain Art \n",
       "5 Cutflower  Sci \n",
       "6 Deadyawn   Art "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "teacher %>% \n",
    "    left_join(dept, by=c(dept=\"id\")) %>%\n",
    "    mutate(flag=case_when(\n",
    "        dept %in% 1:2 ~ 'Sci', \n",
    "        TRUE          ~ 'Art')) %>%\n",
    "    select(name.x, flag)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10.\n",
    "Use CASE to show the name of each teacher followed by 'Sci' if the teacher is in dept 1 or 2, show 'Art' if the teacher's dept is 3 and 'None' otherwise."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 6 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name.x</th><th scope=col>flag</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Shrivell  </td><td>Sci </td></tr>\n",
       "\t<tr><td>Throd     </td><td>Sci </td></tr>\n",
       "\t<tr><td>Splint    </td><td>Sci </td></tr>\n",
       "\t<tr><td>Spiregrain</td><td>None</td></tr>\n",
       "\t<tr><td>Cutflower </td><td>Sci </td></tr>\n",
       "\t<tr><td>Deadyawn  </td><td>None</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 6 × 2\n",
       "\\begin{tabular}{ll}\n",
       " name.x & flag\\\\\n",
       " <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\t Shrivell   & Sci \\\\\n",
       "\t Throd      & Sci \\\\\n",
       "\t Splint     & Sci \\\\\n",
       "\t Spiregrain & None\\\\\n",
       "\t Cutflower  & Sci \\\\\n",
       "\t Deadyawn   & None\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 6 × 2\n",
       "\n",
       "| name.x &lt;chr&gt; | flag &lt;chr&gt; |\n",
       "|---|---|\n",
       "| Shrivell   | Sci  |\n",
       "| Throd      | Sci  |\n",
       "| Splint     | Sci  |\n",
       "| Spiregrain | None |\n",
       "| Cutflower  | Sci  |\n",
       "| Deadyawn   | None |\n",
       "\n"
      ],
      "text/plain": [
       "  name.x     flag\n",
       "1 Shrivell   Sci \n",
       "2 Throd      Sci \n",
       "3 Splint     Sci \n",
       "4 Spiregrain None\n",
       "5 Cutflower  Sci \n",
       "6 Deadyawn   None"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "teacher %>% \n",
    "    left_join(dept, by=c(dept=\"id\")) %>%\n",
    "    mutate(flag=case_when(\n",
    "        dept %in% 1:2 ~ 'Sci',\n",
    "        dept == 3     ~ 'Art',\n",
    "        TRUE          ~ 'None')) %>%\n",
    "    select(name.x, flag)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "dbDisconnect(con)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "R",
   "language": "R",
   "name": "ir"
  },
  "language_info": {
   "codemirror_mode": "r",
   "file_extension": ".r",
   "mimetype": "text/x-r-source",
   "name": "R",
   "pygments_lexer": "r",
   "version": "4.0.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
